﻿/*
TO BE RUN DIRECTLY ON THE LOCALDB DATABASE VIA A QUERY WINDOW
AFTER ENSURING THE CREATE SHADOW TABLE IS IN MAINTNANCE MODE
*/

/*

DELETES THE PersonPhone COLUMN FROM THE Person TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.

*/

-- PERSON SUFFIX WAS NVARCHAR(10) SO LET'S MAKE IT NVARCHAR(20);

--EXECUTE AS USER = 'todocuser';

ALTER TABLE [Person].[Person] ALTER COLUMN [Suffix] NVARCHAR (20);

--REVERT;

-- ALL GOOD. BUT WHAT IF WE NOW TRY AND SHORTEN THE PERSON TYPE COLUMN WHICH HAS A CHECK 
-- CONSTRAINT LOOKING FOR 2 CHARACTER VALUES? CONSTRAINT FAIL. NICE.

--EXECUTE AS USER = 'todocuser';

ALTER TABLE [Person].[Person] ALTER COLUMN  [PersonType] INT;

--REVERT;

-- SO LET'S SHORTERN TITLE WAS NVARCHAR (8). IT'S HAPPY WITH THIS (NO DATA THAT MAY TRUNCATE).

--EXECUTE AS USER = 'todocuser';

ALTER TABLE [Person].[Person] ALTER COLUMN  [Title] NVARCHAR (5);

--REVERT;